package com.iris.live.services.report.sources;

import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: wangzhenyan
 * date: 2017/8/25 14:28
 */
@Component("SalesFunnelDataSource")
public class SalesFunnelDataSource extends BaseReportDataSource{

    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info= context.getParams() ;
        String lineType=info.get("displayType").toString();
        List<Map> datas =new ArrayList<>();
        if("1".equals(lineType)){
            datas = getTimeLineData(info);
        }
        else{
            datas = getOrgLineData(info);
        }

        super.brandGroup(context ,datas);
        return datas;
    }

    private List getTimeLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer("select  " +
                " date, " +
                " sum(clue_num) clue_num,  " +
                " sum(all_inroom_clue_num) all_inroom_clue_num, " +
                " sum(quotation_num) quotation_num, " +
                " sum(order_num) order_num, " +
                " sum(retail_num) retail_num, " +
                " sum(delivery_num) delivery_num, " +
                " sum(cust_flow_num) cust_flow_num, " +
                " sum(first_inroom_drive_num) first_inroom_drive_num, " +
                " sum(inroom_drive_num) inroom_drive_num, " +
                " sum(first_inroom_clue_num) first_inroom_clue_num " +
                "from  " +
                "( select  " +
                "  a.permit, " +
                "  right(a.sale_month,2) as date, " +
                "  a.clue_num , " +
                "  a.all_inroom_clue_num, " +
                "  a.quotation_num, " +
                "  a.order_num, " +
                "  a.retail_num, " +
                "  a.delivery_num, " +
                "  a.first_cust_flow_num + a.second_cust_flow_num cust_flow_num,  " +
                "  a.first_inroom_drive_num,  " +
                "  a.first_inroom_drive_num + a.second_inroom_drive_num inroom_drive_num,  " +
                "  a.first_inroom_clue_num" +
                " from t_rpt_data_sale_info a " +
                " join lsh经销商表 b on a.permit = b.`许可_BMBS` " +
                " join lsh车型 c on a.vehicle_model_id = c.`车型编号` " +
                " where 1=1 ");

        String startMonth = info.get("startMonth") == null ?"":info.get("startMonth").toString(),
               endMonth = info.get("endMonth") == null ?"":info.get("endMonth").toString();
        if (! StringUtils.isNullOrBlank(startMonth)  && ! StringUtils.isNullOrBlank(endMonth)) {
            sql.append("and a.sale_month >= :startMonth and a.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }


        String orgTerm = getOrgTerms("b", info, params);
        if(StringUtils.isNotNullOrBlank(orgTerm)){
            sql.append(" and ").append(orgTerm);
        }

        String carType = info.get("carType") == null ?"":info.get("carType").toString();
        if (! StringUtils.isNullOrBlank(carType) && !"all".equalsIgnoreCase(carType) && !"全部".equalsIgnoreCase(carType)) {
            sql.append(" and (c.品牌 = :carType or c.级别 = :carType or c.车型 = :carType) ");
            params.put("carType",carType);
        }
        sql.append(") e ");
        sql.append("group by e.date ");
        return this.getList(sql.toString(),params);
    }

    private List getOrgLineData(Map info) {
        Map params = new HashMap();
        StringBuffer sql = new StringBuffer("select    " +
                "   'DCH' 集团,许可_LSH,大区 AS 大区, 大区_EN AS 大区_EN, 小区 AS 小区, 小区_EN AS 小区_EN,  " +
                "   IF ( isnull(CTA) OR CTA = '', '其他cta', CTA ) AS CTA,   " +
                "   IF (isnull(CTA_EN) OR CTA_EN = '', 'Other CTA', CTA_EN ) AS CTA_EN,  " +
                "   许可_BMBS AS 许可_BMBS,  " +
                "   经销商名 AS 经销商名,  " +
                "   经销商名_EN AS 经销商名_EN,  " +
                "   SalesCode AS salesCode,  " +
                "   permit," +
                "   sum(clue_num) clue_num,    " +
                "   sum(all_inroom_clue_num) all_inroom_clue_num,   " +
                "   sum(quotation_num) quotation_num,   " +
                "   sum(order_num) order_num,   " +
                "   sum(retail_num) retail_num,   " +
                "   sum(delivery_num) delivery_num,   " +
                "   sum(cust_flow_num) cust_flow_num,   " +
                "   sum(first_inroom_drive_num) first_inroom_drive_num,   " +
                "   sum(inroom_drive_num) inroom_drive_num,   " +
                "   sum(first_inroom_clue_num) first_inroom_clue_num   " +
                "from    " +
                "(  " +
                "   select   " +
                "     'LSH' 集团,b.许可_LSH,b.大区 AS 大区, b.大区_EN AS 大区_EN, b.小区 AS 小区, b.小区_EN AS 小区_EN,  " +
                "     IF ( isnull(b.CTA) OR b.CTA = '', '其他cta', b.CTA ) AS CTA,   " +
                "     IF (isnull(b.CTA_EN) OR b.CTA_EN = '', 'Other CTA', b.CTA_EN ) AS CTA_EN,  " +
                "     b.许可_BMBS AS 许可_BMBS,  " +
                "     b.经销商名 AS 经销商名,  " +
                "     b.经销商名_EN AS 经销商名_EN,  " +
                "     b.SalesCode AS salesCode,   " +
                "     a.permit," +
                "     a.clue_num ,   " +
                "     a.all_inroom_clue_num,   " +
                "     a.quotation_num,   " +
                "     a.order_num,   " +
                "     a.retail_num,   " +
                "     a.delivery_num,   " +
                "     a.first_cust_flow_num + a.second_cust_flow_num cust_flow_num,    " +
                "     a.first_inroom_drive_num,    " +
                "     a.first_inroom_drive_num + a.second_inroom_drive_num inroom_drive_num,    " +
                "     a.first_inroom_clue_num  " +
                "   from t_rpt_data_sale_info a   " +
                "   join lsh经销商表 b on a.permit = b.`许可_BMBS`   " +
                "   join lsh车型 c on a.vehicle_model_id = c.`车型编号`   " +
                "   where 1=1 ");

        String startMonth = info.get("startMonth") == null ?"":info.get("startMonth").toString(),
                endMonth = info.get("endMonth") == null ?"":info.get("endMonth").toString();
        if (! StringUtils.isNullOrBlank(startMonth)  && ! StringUtils.isNullOrBlank(endMonth)) {
            sql.append("and a.sale_month >= :startMonth and a.sale_month <= :endMonth ");
            params.put("startMonth",startMonth);
            params.put("endMonth",endMonth);
        }

        String carType = info.get("carType") == null ?"":info.get("carType").toString();
        if (! StringUtils.isNullOrBlank(carType) && !"all".equalsIgnoreCase(carType) && !"全部".equalsIgnoreCase(carType)) {
            sql.append("and (c.品牌 = :carType or c.级别 = :carType or c.车型 = :carType) ");
            params.put("carType",carType);
        }

        sql.append(") e ");
        sql.append("group by e.permit ");
        return this.getList(sql.toString(),params);
    }

}
